﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using SmartSpace.Core;
using SmartSpace.Data;

namespace SmartSpace.Workflow.Util
{
    public class WfDbScriptUtil
    {
        public static string GetPagedListScript(
            WorkflowMetadata workflow,
            ActivityMetadata activity,
            ViewMetadata view,
            WfViewType viewMode,
            WfState state,
            User user,
            int start,
            int limit,
            DbCommand command,
            out List<DbParameter> parameters)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            parameters = new List<DbParameter>();

            StringBuilder queryBuilder = new StringBuilder();
            StringBuilder queryTempBuilder = new StringBuilder();
            StringBuilder conditionBuilder = new StringBuilder();

            for (int index = 0; index < view.Fields.Count; index++)
            {
                SmartSpace.Data.FieldMetadata field = view.Fields[index];
                if (field.CanVisit || field.Property.IsIdentity)
                {
                    queryBuilder.AppendFormat("{0},", GetPropertyName(field.Property));
                    queryTempBuilder.AppendFormat("ent.{0},", GetPropertyName(field.Property));

                    if (view.Fields[index].Property.Type.IsComplex)
                    {
                        queryBuilder.AppendFormat("{0},", GetPropertyTextName(view.Fields[index].Property));
                        queryTempBuilder.AppendFormat("ent.{0},", GetPropertyTextName(view.Fields[index].Property));
                    }
                }
            }
            queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
            // 之后要接 rownumber 列 需要‘，’
            //queryTempBuilder = queryTempBuilder.Remove(queryTempBuilder.Length - 1, 1);

            if (activity.IsRoot)
            {
                #region 草稿
                DbParameter receiverIdParam = command.CreateParameter();
                receiverIdParam.ParameterName = "@p2";
                receiverIdParam.Value = user.Id;
                parameters.Add(receiverIdParam);

                // 根节点的活动不关联到流程流转情况
                // 该位置的数据属于草稿数据
                sqlBuilder.AppendFormat(
@"select {0} from (select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent where ent.__Creator=@p2 and ent.__Id not in (select dataid as __Id from __WorkflowInstance as wi)) as k where rowIndex between {4} and {5}",
                    queryBuilder,
                    queryTempBuilder,
                    GetPropertyName(workflow.Entity.GetIdentity()),
                    GetTableName(workflow.Entity),
                    start, // 1-25, 26-50
                    start + limit - 1);
                #endregion
            }
            else
            {
//                if (viewMode == WfViewType.ListEdit)
//                {
//                    #region 所有任务
//                    DbParameter activityIdParam = command.CreateParameter();
//                    activityIdParam.ParameterName = "@p1";
//                    activityIdParam.Value = activity.Id;
//                    parameters.Add(activityIdParam);

//                    DbParameter receiverIdParam = command.CreateParameter();
//                    receiverIdParam.ParameterName = "@p2";
//                    receiverIdParam.Value = user.Id;
//                    parameters.Add(receiverIdParam);

//                    sqlBuilder.AppendFormat(
//@"select {0} from (
//select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent 
//	join __WorkflowInstance as wi on wi.DataId = ent.__ID
//	join __WorkflowDriveRecord as wdr on wdr.InstanceId = wi.Id
//where wi.ActivityId=@p1
//    and (wdr.SenderId=@p2 or wdr.ReceiverId=@p2)) as k where rowIndex between {4} and {5}",
//                        queryBuilder,
//                        queryTempBuilder,
//                        GetPropertyName(workflow.Entity.GetIdentity()),
//                        GetTableName(workflow.Entity),
//                        start, // 1-25, 26-50
//                        start + limit - 1);
//                    #endregion
//                }
//                else 
                if (state == WfState.Processed)
                {
                    #region 已办理任务
                    DbParameter activityIdParam = command.CreateParameter();
                    activityIdParam.ParameterName = "@p1";
                    activityIdParam.Value = activity.Id;
                    parameters.Add(activityIdParam);

                    DbParameter receiverIdParam = command.CreateParameter();
                    receiverIdParam.ParameterName = "@p2";
                    receiverIdParam.Value = user.Id;
                    parameters.Add(receiverIdParam);

                    sqlBuilder.AppendFormat(
@"select {0} from (
select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent 
	join __WorkflowInstance as wi on wi.DataId = ent.__ID
	join __WorkflowDriveRecord as wdr on wdr.InstanceId = wi.Id
where wi.ActivityId=@p1
    and wdr.IsActivated=0
    and wdr.SenderId=@p2) as k where rowIndex between {4} and {5}",
                        queryBuilder,
                        queryTempBuilder,
                        GetPropertyName(workflow.Entity.GetIdentity()),
                        GetTableName(workflow.Entity),
                        start, // 1-25, 26-50
                        start + limit - 1);
                    #endregion
                }
                else if (state == WfState.Processing)
                {
                    #region 待办理任务
                    DbParameter activityIdParam = command.CreateParameter();
                    activityIdParam.ParameterName = "@p1";
                    activityIdParam.Value = activity.Id;
                    parameters.Add(activityIdParam);

                    DbParameter receiverIdParam = command.CreateParameter();
                    receiverIdParam.ParameterName = "@p2";
                    receiverIdParam.Value = user.Id;
                    parameters.Add(receiverIdParam);

                    sqlBuilder.AppendFormat(
@"select {0} from (
select {1} row_number() over(order BY ent.{2}) rowIndex from {3} as ent 
	join __WorkflowInstance as wi on wi.DataId = ent.__ID
	join __WorkflowDriveRecord as wdr on wdr.InstanceId = wi.Id
where wi.ActivityId=@p1
    and wdr.IsActivated=1
    and wdr.ReceiverId=@p2) as k where rowIndex between {4} and {5}",
                        queryBuilder,
                        queryTempBuilder,
                        GetPropertyName(workflow.Entity.GetIdentity()),
                        GetTableName(workflow.Entity),
                        start, // 1-25, 26-50
                        start + limit - 1);
                    #endregion
                }
            }

            return sqlBuilder.ToString();
        }

        //public static string GetUpdateScript(DataInstance data, DbCommand command, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    bool isFirstColumn = true;
        //    parameters = new List<DbParameter>();

        //    sqlBuilder.AppendFormat("UPDATE {0} SET ", GetTableName(data.Entity));
        //    for (int index = 0; index < data.Fields.Count; index++)
        //    {
        //        FieldInstance field = data.Fields[index];
        //        if (!field.Property.IsIdentity)
        //        {
        //            if (isFirstColumn)
        //            {
        //                isFirstColumn = false;
        //            }
        //            else
        //            {
        //                sqlBuilder.Append(",");
        //            }
        //            sqlBuilder.AppendFormat("{0}=@p{1}", GetPropertyName(field.Property), index);
                    

        //            DbParameter parameter = command.CreateParameter();
        //            parameter.ParameterName = string.Format("@p{0}", index);
        //            parameter.Value = field.Value;
        //            parameters.Add(parameter);
        //        }
        //    }
        //    sqlBuilder.AppendFormat(" WHERE {0}=@Pid", GetPropertyName(data.GetIdentity().Property));
        //    DbParameter parameterId = command.CreateParameter();
        //    parameterId.ParameterName = "@Pid";
        //    parameterId.Value = data.Id;
        //    parameters.Add(parameterId);

        //    return sqlBuilder.ToString();
        //}

        //public static string GetInsertScript(DataInstance data, DbCommand command, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    StringBuilder nameBuilder = new StringBuilder();
        //    StringBuilder valueBuilder = new StringBuilder();
        //    parameters = new List<DbParameter>();

        //    sqlBuilder.AppendFormat("INSERT INTO {0} ", GetTableName(data.Entity));

        //    nameBuilder.AppendFormat("{0}", GetPropertyName(data.GetIdentity().Property));
        //    valueBuilder.Append("@Pid");
        //    DbParameter parameterId = command.CreateParameter();
        //    parameterId.ParameterName = "@Pid";
        //    parameterId.Value = data.Id;
        //    parameters.Add(parameterId);

        //    for (int index = 0; index < data.Fields.Count; index++)
        //    {
        //        FieldInstance field = data.Fields[index];
        //        if (!field.Property.IsIdentity)
        //        {
        //            nameBuilder.AppendFormat(",{0}", GetPropertyName(field.Property));
        //            valueBuilder.AppendFormat(",@p{0}", index);


        //            DbParameter parameter = command.CreateParameter();
        //            parameter.ParameterName = string.Format("@p{0}", index);
        //            parameter.Value = field.Value;
        //            parameters.Add(parameter);
        //        }
        //    }
        //    sqlBuilder.AppendFormat("({0}) VALUES ({1})", nameBuilder, valueBuilder);

        //    return sqlBuilder.ToString();
        //}

        //public static string GetSelectScript(ViewMetadata view, Guid identity, DbCommand command, out List<DbParameter> parameters)
        //{
        //    StringBuilder sqlBuilder = new StringBuilder();
        //    parameters = new List<DbParameter>();

        //    StringBuilder queryBuilder = new StringBuilder();
        //    StringBuilder conditionBuilder = new StringBuilder();

        //    for (int index = 0; index < view.Fields.Count; index++)
        //    {
        //        queryBuilder.AppendFormat("{0},", GetPropertyName(view.Fields[index].Property));
        //    }
        //    queryBuilder = queryBuilder.Remove(queryBuilder.Length - 1, 1);
        //    sqlBuilder.AppendFormat(
        //        "select {0} from {1} where {2}=@p1",
        //        queryBuilder,
        //        GetTableName(view.Entity),
        //        GetPropertyName(view.Entity.GetIdentity()));

        //    DbParameter identityParam = command.CreateParameter();
        //    identityParam.ParameterName = "@p1";
        //    identityParam.Value = identity;
        //    parameters.Add(identityParam);

        //    return sqlBuilder.ToString();
        //}

        private static string GetTableName(EntityMetadata entity)
        {
            return string.Format("[{0}]", entity.Name);
        }

        private static string GetPrimaryKeyName(EntityMetadata entity)
        {
            return string.Format("[PK_{0}]", entity.Name);
        }

        public static string GetPropertyName(PropertyMetadata property)
        {
            return string.Format("[{0}]", property.Name);
        }

        private static string GetPropertyTextName(PropertyMetadata property)
        {
            return string.Format("[{0}_text]", property.Name);
        }

        private static string GetPropertyType(PropertyMetadata property)
        {
            TypeMetadata type = property.Type;
            if (property.MaxLength != 0)
            {
                return string.Format("[{0}]({1})", type.DbType, property.MaxLength);
            }
            else
            {
                return string.Format("[{0}]", type.DbType);
            }
        }
    }
}
